/*
 * MIT License
 *
 * Copyright (c) 2023 北京凯特伟业科技有限公司
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */
// Copyright tang.  All rights reserved.
// https://gitee.com/inrgihc/dbswitch
//
// Use of this source code is governed by a BSD-style license
//
// Author: tang (inrgihc@126.com)
// Date : 2020/1/2
// Location: beijing , china
/////////////////////////////////////////////////////////////
package com.je.meta.service.dbswitch.service;

import com.google.common.base.Strings;
import com.je.common.base.DynaBean;
import com.je.common.base.mapper.query.NativeQuery;
import com.je.common.base.service.CommonService;
import com.je.common.base.service.MetaResourceService;
import com.je.common.base.service.MetaService;
import com.je.common.base.spring.SpringContextHolder;
import com.je.ibatis.extension.metadata.IdType;
import com.je.ibatis.extension.plugins.pagination.Page;
import com.je.meta.model.database.*;
import com.je.meta.model.database.type.ProductTypeEnum;
import com.je.meta.rpc.dbswitch.DbSwitchRpcService;
import com.je.meta.util.database.DbswitchStrUtils;
import com.je.meta.util.database.HivePrepareUtils;
import com.je.meta.util.database.TypeConvertUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

/**
 * 数据库元信息抽象基类
 *
 * @author tang
 */
public abstract class AbstractDatabase implements IDatabaseInterface {

    public static final int CLOB_LENGTH = 9999999;

    protected String driverClassName;
    protected String catalogName = null;
    protected MetaService metaService;
    protected CommonService commonService;
    private DbSwitchRpcService dbSwitchRpcService;
    private MetaResourceService metaResourceService;


    public AbstractDatabase(String driverClassName) {
        try {
            this.driverClassName = driverClassName;
            this.metaService = SpringContextHolder.getBean(MetaService.class);
            this.commonService = SpringContextHolder.getBean(CommonService.class);
            this.dbSwitchRpcService = SpringContextHolder.getBean("dbSwitchRpcService");
            this.metaResourceService = SpringContextHolder.getBean(MetaResourceService.class);
            Class.forName(driverClassName);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<String> querySchemaList(Connection connection) {
        Set<String> ret = new LinkedHashSet<>();
        try (ResultSet schemas = connection.getMetaData().getSchemas()) {
            while (schemas.next()) {
                ret.add(schemas.getString("TABLE_SCHEM"));
            }
            return new ArrayList<>(ret);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<TableDescription> queryTableList(Connection connection, String schemaName) {
        List<TableDescription> ret = new ArrayList<>();
        Set<String> uniqueSet = new LinkedHashSet<>();
        String[] types = new String[]{"TABLE", "VIEW"};
        try (ResultSet tables = connection.getMetaData()
                .getTables(this.catalogName, schemaName, "%", types)) {
            while (tables.next()) {
                String tableCode = tables.getString("TABLE_NAME");
                if (uniqueSet.contains(tableCode)) {
                    continue;
                } else {
                    uniqueSet.add(tableCode);
                }
                TableDescription td = new TableDescription();
                td.setSchemaName(schemaName);
                td.setTableCode(tableCode);
                td.setTableName(tables.getString("REMARKS"));
                td.setTableType(tables.getString("TABLE_TYPE").toUpperCase());
                ret.add(td);
            }
            return ret;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Page queryTableList(Connection connection, String schemaName, String productId, String fuzzyValue
            , int intPage, int intLimit) {
        List<TableDescription> ret = new ArrayList<>();
        Set<String> uniqueSet = new LinkedHashSet<>();
        String[] types = new String[]{"TABLE", "VIEW"};
        List<String> metaTablesCode = getJeMetaTablesCode(productId);
        try (ResultSet tables = connection.getMetaData()
                .getTables(this.catalogName, schemaName, "%", types)) {
            while (tables.next()) {
                String tableCode = tables.getString("TABLE_NAME");
                if (uniqueSet.contains(tableCode)) {
                    continue;
                } else {
                    uniqueSet.add(tableCode);
                }

                TableDescription td = new TableDescription();
                td.setSchemaName(schemaName);
                if (metaTablesCode.contains(tableCode.toUpperCase())) {
                    td.setMetaIsCreate("1");
                    continue;
                } else {
                    td.setMetaIsCreate("0");
                }
                td.setTableCode(tableCode);
                td.setTableName(tables.getString("REMARKS"));
                td.setTableType(tables.getString("TABLE_TYPE").toUpperCase());
                if (isFuzzyValue(td, fuzzyValue)) {
                    ret.add(td);
                }
            }
            return getPage(ret, intPage, intLimit);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 简单分页
     *
     * @param pagingList
     * @param pageNum
     * @param pageSize
     * @return
     */
    public static Page getPage(List<TableDescription> pagingList, int pageNum, int pageSize) {
        Page resultPage = new Page(pageNum, pageSize);
        resultPage.setTotal(pagingList.size());
        resultPage.setRecords(subList(pagingList, pageNum, pageSize));
        return resultPage;
    }

    private static <E> List<E> subList(List<E> list, int pageNum, int pageSize) {
        int size = list.size();
        List<E> result = new ArrayList<>();
        int idx = (pageNum - 1) * pageSize;
        int end = idx + pageSize;
        while (idx < size && idx < end) {
            result.add(list.get(idx));
            idx++;
        }
        return result;
    }

    /**
     * 模糊查询
     *
     * @return
     */
    protected Boolean isFuzzyValue(TableDescription td, String value) {
        if (!Strings.isNullOrEmpty(value)) {
            if (!Strings.isNullOrEmpty(td.getTableName()) && td.getTableName().toUpperCase().contains(value.toUpperCase())) {
                return true;
            }
            if (td.getTableCode().toUpperCase().contains(value.toUpperCase())) {
                return true;
            }
        } else {
            return true;
        }
        return false;
    }

    protected List<String> getJeMetaTablesCode(String productId) {
        if (Strings.isNullOrEmpty(productId)) {
            return new ArrayList<>();
        }
        List<String> codes = new ArrayList<>();
        List<DynaBean> list = metaResourceService.selectByTableCodeAndNativeQuery("JE_CORE_RESOURCETABLE", NativeQuery.build()
                .eq("SY_PRODUCT_ID", productId));
        for (DynaBean dynaBean : list) {
            codes.add(dynaBean.getStr("RESOURCETABLE_TABLECODE").toUpperCase());
        }
        return codes;
    }

    @Override
    public TableDescription queryTableMeta(Connection connection, String schemaName,
                                           String tableName) {
        return queryTableList(connection, schemaName).stream()
                .filter(one -> tableName.equals(one.getTableCode()))
                .findAny().orElse(null);
    }

    @Override
    public void buildTableKeyGenerator(TableDescription td, List<String> pks, List<ColumnDescription> columns) {
        if (pks != null && pks.size() > 0) {
            String pkCode = pks.get(0);
            for (ColumnDescription columnDescription : columns) {
                if (pkCode.equals(columnDescription.getFieldName())) {
                    if (columnDescription.isAutoIncrement()) {
                        td.setKeyGeneratorType(IdType.AUTO.getValue());
                        td.setKeyGeneratorName("自增");
                    } else {
                        td.setKeyGeneratorType(IdType.UUID.getValue());
                        td.setKeyGeneratorName(IdType.UUID.getValue());
                    }
                }
            }
        } else {
            td.setKeyGeneratorType(IdType.UUID.getValue());
            td.setKeyGeneratorName(IdType.UUID.getValue());
        }
    }

    @Override
    public List<ColumnDescription> queryTableColumnMeta(Connection connection, String schemaName,
                                                        String tableName) {
        String sql = this.getTableFieldsQuerySQL(schemaName, tableName);
        List<ColumnDescription> ret = this.querySelectSqlColumnMeta(connection, sql);

        // 补充一下注释信息
        try (ResultSet columns = connection.getMetaData()
                .getColumns(this.catalogName, schemaName, tableName, null)) {
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                String remarks = columns.getString("REMARKS");
                for (ColumnDescription cd : ret) {
                    if (columnName.equals(cd.getFieldName())) {
                        cd.setRemarks(remarks);
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return ret;
    }

    @Override
    public List<String> queryTablePrimaryKeys(Connection connection, String schemaName,
                                              String tableName) {
        Set<String> ret = new LinkedHashSet<>();
        try (ResultSet primaryKeys = connection.getMetaData()
                .getPrimaryKeys(this.catalogName, schemaName, tableName)) {
            while (primaryKeys.next()) {
                ret.add(primaryKeys.getString("COLUMN_NAME"));
            }
            return new ArrayList<>(ret);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public SchemaTableData queryTableData(Connection connection, String schemaName, String tableName,
                                          int rowCount) {
        String fullTableName = getQuotedSchemaTableCombination(schemaName, tableName);
        String querySQL = String.format("SELECT * FROM %s ", fullTableName);
        SchemaTableData data = new SchemaTableData();
        data.setSchemaName(schemaName);
        data.setTableName(tableName);
        data.setColumns(new ArrayList<>());
        data.setRows(new ArrayList<>());
        try (Statement st = connection.createStatement()) {
            if (getDatabaseType() == ProductTypeEnum.HIVE) {
                HivePrepareUtils.prepare(connection, schemaName, tableName);
            }

            try (ResultSet rs = st.executeQuery(querySQL)) {
                ResultSetMetaData m = rs.getMetaData();
                int count = m.getColumnCount();
                for (int i = 1; i <= count; i++) {
                    data.getColumns().add(m.getColumnLabel(i));
                }

                int counter = 0;
                while (rs.next() && counter++ < rowCount) {
                    List<Object> row = new ArrayList<>(count);
                    for (int i = 1; i <= count; i++) {
                        Object value = rs.getObject(i);
                        if (value instanceof byte[]) {
                            row.add(DbswitchStrUtils.toHexString((byte[]) value));
                        } else if (value instanceof java.sql.Clob) {
                            row.add(TypeConvertUtils.castToString(value));
                        } else if (value instanceof java.sql.Blob) {
                            byte[] bytes = TypeConvertUtils.castToByteArray(value);
                            row.add(DbswitchStrUtils.toHexString(bytes));
                        } else {
                            row.add(null == value ? null : value.toString());
                        }
                    }
                    data.getRows().add(row);
                }

                return data;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void testQuerySQL(Connection connection, String sql) {
        String wrapperSql = this.getTestQuerySQL(sql);
        try (Statement statement = connection.createStatement()) {
            statement.execute(wrapperSql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public String getQuotedSchemaTableCombination(String schemaName, String tableName) {
        return String.format(" \"%s\".\"%s\" ", schemaName, tableName);
    }

    @Override
    public String getFieldDefinition(ColumnMetaData v, List<String> pks, boolean useAutoInc,
                                     boolean addCr, boolean withRemarks) {
        throw new RuntimeException("AbstractDatabase Unimplemented!");
    }

    @Override
    public String getPrimaryKeyAsString(List<String> pks) {
        if (!pks.isEmpty()) {
            StringBuilder sb = new StringBuilder();
            sb.append("\"");
            sb.append(StringUtils.join(pks, "\" , \""));
            sb.append("\"");
            return sb.toString();
        }

        return "";
    }

    @Override
    public List<String> getTableColumnCommentDefinition(TableDescription td,
                                                        List<ColumnDescription> cds) {
        throw new RuntimeException("AbstractDatabase Unimplemented!");
    }

    /**************************************
     * internal function
     **************************************/

    protected abstract String getTableFieldsQuerySQL(String schemaName, String tableName);

    protected abstract String getTestQuerySQL(String sql);

    protected List<ColumnDescription> getSelectSqlColumnMeta(Connection connection, String querySQL) {
        List<ColumnDescription> ret = new ArrayList<>();
        try (Statement st = connection.createStatement()) {
            if (getDatabaseType() == ProductTypeEnum.HIVE) {
                HivePrepareUtils.setResultSetColumnNameNotUnique(connection);
            }

            try (ResultSet rs = st.executeQuery(querySQL)) {
                ResultSetMetaData m = rs.getMetaData();
                int columns = m.getColumnCount();
                for (int i = 1; i <= columns; i++) {
                    String name = m.getColumnLabel(i);
                    if (null == name) {
                        name = m.getColumnName(i);
                    }

                    ColumnDescription cd = new ColumnDescription();
                    cd.setFieldName(name);
                    cd.setLabelName(name);
                    cd.setFieldType(m.getColumnType(i));
                    if (0 != cd.getFieldType()) {
                        cd.setFieldTypeName(m.getColumnTypeName(i));
                        cd.setFiledTypeClassName(m.getColumnClassName(i));
                        cd.setDisplaySize(m.getColumnDisplaySize(i));
                        cd.setPrecisionSize(m.getPrecision(i));
                        cd.setScaleSize(m.getScale(i));
                        cd.setAutoIncrement(m.isAutoIncrement(i));
                        cd.setNullable(m.isNullable(i) != ResultSetMetaData.columnNoNulls);
                    } else {
                        // 处理视图中NULL as fieldName的情况
                        cd.setFieldTypeName("CHAR");
                        cd.setFiledTypeClassName(String.class.getName());
                        cd.setDisplaySize(1);
                        cd.setPrecisionSize(1);
                        cd.setScaleSize(0);
                        cd.setAutoIncrement(false);
                        cd.setNullable(true);
                    }

                    boolean signed = false;
                    try {
                        signed = m.isSigned(i);
                    } catch (Exception ignored) {
                        // This JDBC Driver doesn't support the isSigned method
                        // nothing more we can do here by catch the exception.
                    }
                    cd.setSigned(signed);
                    cd.setProductType(getDatabaseType());

                    ret.add(cd);
                }

                return ret;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<ImportedKeyData> getTableImportedKeyData(Connection connection, String schemaName, String tableName, SchemaTableMeta tableMeta) {
        Set<ImportedKeyData> ret = new LinkedHashSet<>();
        try (ResultSet foreignKey = connection.getMetaData()
                .getImportedKeys(this.catalogName, schemaName, tableName)) {
            while (foreignKey.next()) {
                ImportedKeyData importedKeyData = ImportedKeyData.buildDataByResult(foreignKey, tableMeta);
                ret.add(importedKeyData);
            }
            return new ArrayList<>(ret);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<IndexesData> getTableIndexesDataData(Connection connection, String schemaName, String tableName, List<ColumnDescription> columns) {
        Set<IndexesData> ret = new LinkedHashSet<>();
        try (ResultSet indexInfo = connection.getMetaData()
                .getIndexInfo(this.catalogName, schemaName, tableName, false, false)) {
            while (indexInfo.next()) {
                IndexesData indexesData = new IndexesData();
                if (indexInfo.getString("INDEX_NAME").equals("PRIMARY")) {
                    continue;
                }
                indexesData.setCode(indexInfo.getString("COLUMN_NAME"));
                for (ColumnDescription column : columns) {
                    String code = column.getFieldName();
                    if (code.equals(indexInfo.getString("COLUMN_NAME"))) {
                        indexesData.setName(column.getLabelName());
                        break;
                    }
                }
                indexesData.setIndexName(indexInfo.getString("INDEX_NAME"));
                indexesData.setTableCode(tableName);
                String unique = indexInfo.getString("NON_UNIQUE");
                if (unique.equals("true")) {
                    indexesData.setUnique("0");
                } else {
                    indexesData.setUnique("1");
                }
                ret.add(indexesData);
            }
            return new ArrayList<>(ret);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void updateJeTableMetaBySchemaTableMeta(List<SchemaTableMeta> list, String syParent, String productId) {
        dbSwitchRpcService.updateMetaDataTablesInfo(list, syParent, productId);
    }

}